﻿--Sequence: public.candidate_seq

--DROP SEQUENCE public.candidate_seq;

CREATE SEQUENCE public.candidate_seq
  START 1
  INCREMENT 1
  MINVALUE 9223372036854775807
  MAXVALUE 1
  CACHE 1;

ALTER TABLE public.candidate_seq
  OWNER TO "admin";

--Table: public.candidate

--DROP TABLE public.candidate;

CREATE TABLE public.candidate (
  id             integer NOT NULL,
  firstname      varchar(30),
  lastname       varchar(30),
  numberofvotes  integer,
  /* Keys */
  CONSTRAINT candidate_pkey
    PRIMARY KEY (id)
) WITH (
    OIDS = FALSE
  );

ALTER TABLE public.candidate
  OWNER TO postgres;
  
  
  
  --Table: public.codedVotes

--DROP TABLE public."codedVotes";

CREATE TABLE public."codedVotes" (
  data    text,
  "time"  timestamp WITHOUT TIME ZONE
) WITH (
    OIDS = FALSE
  );

ALTER TABLE public."codedVotes"
  OWNER TO "admin";
  
  --Table: public.voter

--DROP TABLE public.voter;

CREATE TABLE public.voter (
  firstname       varchar(30),
  lastname        varchar(30),
  pesel           varchar(11) NOT NULL,
  secretcode      text,
  hascertificate  boolean,
  hascard         boolean,
  /* Keys */
  CONSTRAINT voter_pkey
    PRIMARY KEY (pesel), 
  CONSTRAINT c1
    UNIQUE (secretcode)
) WITH (
    OIDS = FALSE
  );

ALTER TABLE public.voter
  OWNER TO postgres;
  
  
  --Table: public.votes

--DROP TABLE public.votes;

CREATE TABLE public.votes (
  voteid          text,
  candidateid     integer,
  "time"          timestamp WITHOUT TIME ZONE,
  originaldigest  text,
  /* Keys */
  CONSTRAINT votes_originaldigest_key
    UNIQUE (originaldigest)
) WITH (
    OIDS = FALSE
  );

ALTER TABLE public.votes
  OWNER TO postgres;
  
  
  
  --Function: public.isvalid(firstn varchar, lastn varchar, pes varchar, code varchar)

--DROP FUNCTION public.isvalid(firstn varchar, lastn varchar, pes varchar, code varchar);

CREATE OR REPLACE FUNCTION public.isvalid
(
  firstn  varchar,
  lastn   varchar,
  pes     varchar,
  code    varchar
)
RETURNS boolean AS
$$
--sprawdza czy dane glosujacego sa poprawne
begin
	if exists(select * from voter where firstName=firstN and lastName=lastN and pesel=pes and secretCode=code and hascertificate='f')
		then 
		begin
			UPDATE voter SET hascertificate='t' WHERE firstName=firstN and lastName=lastN and pesel=pes and secretCode=code;
			return true;
		end;
	else
		return false;
	end if;
end;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.isvalid(firstn varchar, lastn varchar, pes varchar, code varchar)
  OWNER TO postgres;
  
  
  
  --Function: public.publicate(Input id integer, Input firstname varchar, Input lastname varchar, Input numberofvotes integer)

--DROP FUNCTION public.publicate(IN id integer, IN firstname varchar, IN lastname varchar, IN numberofvotes integer);

CREATE OR REPLACE FUNCTION public.publicate
(
  IN  id             integer,
  IN  firstname      varchar,
  IN  lastname       varchar,
  IN  numberofvotes  integer
)
AS
$$
--publikacja wynikow
BEGIN
	RETURN QUERY SELECT * from candidate order by numberOfVotes desc;
end;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.publicate(IN id integer, IN firstname varchar, IN lastname varchar, IN numberofvotes integer)
  OWNER TO postgres;
  
  --Function: public.register(vid text, cid integer, digest text)

--DROP FUNCTION public.register(vid text, "cid" integer, digest text);

CREATE OR REPLACE FUNCTION public.register
(
  vid     text,
  "cid"   integer,
  digest  text
)
RETURNS boolean AS
$$
-- zapisuje glos, zwraca t jesli sie powiodlo, f jesli nie
declare
	num integer :=(select numberOfVotes from candidate where id=cId);
begin
	num :=num+1;
	if exists (select voteId from votes where voteId=vId) -- jezeli glos o takim id zostal juz oddany
		then return false;
	elseif not exists (select id from candidate where id=cId) --jezeli nie ma takiego kandydata
		then return false;
	else								--w przeciwnym wypadku
		insert into votes values (vId, cId, current_timestamp, digest); --zapisz glos z aktualna data i czasem
		update candidate set numberOfVotes=num where id=cId;
		return true;
	end if;
end;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.register(vid text, "cid" integer, digest text)
  OWNER TO "admin";
  
  
  
  --Function: public.register(vid text, cid integer)

--DROP FUNCTION public.register(vid text, "cid" integer);

CREATE OR REPLACE FUNCTION public.register
(
  vid    text,
  "cid"  integer
)
RETURNS boolean AS
$$
-- zapisuje glos, zwraca t jesli sie powiodlo, f jesli nie
declare
	num integer :=(select numberOfVotes from candidate where id=cId);
begin
	num :=num+1;
	if exists (select voteId from votes where voteId=vId) -- jezeli glos o takim id zostal juz oddany
		then return false;
	elseif not exists (select id from candidate where id=cId) --jezeli nie ma takiego kandydata
		then return false;
	else								--w przeciwnym wypadku
		insert into votes(voteid, candidateid, time) values (vId, cId, current_timestamp); --zapisz glos z aktualna data i czasem
		update candidate set numberOfVotes=num where id=cId;
		return true;
	end if;
end;
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.register(vid text, "cid" integer)
  OWNER TO postgres;
  
  
  
  
  